package analyse;

import generator.Persistence;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

import analyse.database.object.ConnectionDbo;
import analyse.database.object.RefreshInfoDbo;
import analyse.database.object.RefreshInfoLogDbo;
import analyse.database.object.TableDefDbo;
import db.AbstractDbNativeStatements;
import db.Db2NativeStatements;
import db.MySqlNativeStatements;
import db.OracleNativeStatements;
import db.type.Column;

public class Analyse {
	
	private static org.apache.log4j.Logger log = Logger.getLogger(Persistence.class);
	
	private AbstractDbNativeStatements dbStatements = null;
	
	private DefaultDao analyseDbDao = new DefaultDao();
	
	private Connection connection = null;
	
	
	public static void main(String[] args) {
		Analyse analyse = new Analyse();
		try {
			analyse.start();
		} catch (SQLException e) {
			log.error("", e);
		}
	}
	
	private void start() throws SQLException {

		log.info("Analyse started");
		

		
		
		ArrayList<ConnectionDbo> connections = 
			analyseDbDao.findBySqlReturnArrayList(
				ConnectionDbo.class, 
				"SELECT * FROM connection ", 
				null);
		
		for (int i = 0; i < connections.size(); i++) {
			
			ConnectionDbo connectionToAnalyse = connections.get(i);
			
			RefreshInfoDbo refreshInfo = new RefreshInfoDbo();
			refreshInfo.setCreated(new Timestamp(System.currentTimeMillis()));
			refreshInfo.setConId(connectionToAnalyse.getId());
			analyseDbDao.save(refreshInfo);
			
			if (connectionToAnalyse.getDriverClass() == null) {
				log.error("no driver class specified: " + connectionToAnalyse.getDriverClass());
				System.exit(-1);
			}
			
			
			if (connectionToAnalyse.getDriverClass().indexOf("mysql") >= 0) {
				dbStatements = new MySqlNativeStatements();
			}
			if (connectionToAnalyse.getDriverClass().indexOf("oracle") >= 0) {
				dbStatements = new OracleNativeStatements();
			}
			if (connectionToAnalyse.getDriverClass().indexOf("db2") >= 0) {
				dbStatements = new Db2NativeStatements();
			}
	
			if (dbStatements == null) {
				log.error("no databasetyp identified by driver class: " + connectionToAnalyse.getDriverClass());
				System.exit(-1);
			}		
			
			DataSource dataSource = setupDataSource(
					connectionToAnalyse.getDriverClass(),
					connectionToAnalyse.getDatabaseUrl(), 
					connectionToAnalyse.getDatabaseSchema(), 
					connectionToAnalyse.getUser(), 
					connectionToAnalyse.getPassword());
			log.debug("setup dataSource");
	
			try {
				connection = dataSource.getConnection();
				log.debug("connection created: " + dataSource.getConnection().toString());
				
				ArrayList<String> allTables = getAllTables(connectionToAnalyse.getDatabaseSchema());
				
				for (int j = 0; j < allTables.size(); j++) {
					String table = (String) allTables.get(j);
					
					ArrayList columns = 
						getAllColumns(
								connectionToAnalyse.getDatabaseSchema(), 
								table);
					
					ArrayList<RefreshInfoLogDbo> refreshResult = refreshTableDef(connectionToAnalyse, table, columns);
					for (int k = 0; k < refreshResult.size(); k++) {
						refreshResult.get(k).setRefId(refreshInfo.getId());
						analyseDbDao.save(refreshResult.get(k));
					}
					//generateDO(table, columns);
					
				}
			} catch (SQLException e) {
				log.error("", e);
			} finally {
				try {
					connection.close();
				} catch (Exception e) {
				}
			}
		}
		
		log.info("Generator finished");
	}

	private DataSource setupDataSource(String driverClass, String connectionString, String schema, String user, String password) {
		BasicDataSource ds = new BasicDataSource();
		ds.setDriverClassName(driverClass);
		
		if (dbStatements instanceof MySqlNativeStatements) {
			StringBuffer connectionUri = new StringBuffer();
			connectionUri.append("jdbc:mysql://localhost/");
			connectionUri.append(schema);
			connectionUri.append("?").append("user=").append(user);
			if (password != null) {
				connectionUri.append("&password=").append(password);
			}
			ds.setUrl(connectionUri.toString());
		}
		
		if (dbStatements instanceof OracleNativeStatements) {
			ds.setUsername(user);
			ds.setUrl(connectionString);
			if (password != null) {
				ds.setPassword(password);
			}
		}
		
		return ds;
	}

	private ArrayList<String> getAllTables(String schema) {
		ArrayList<String> result = new ArrayList<String>();

		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {

			stmt = connection.createStatement();
			log.debug("execute select table query");
			
			
			sql= dbStatements.getAllTablesSql(schema);
			log.debug("read all tables sql: " + sql);
			rset = stmt.executeQuery(sql);
			

			while (rset.next()) {
				result.add(rset.getString("table_name"));
			}

		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;
	}
	

	
	private ArrayList<RefreshInfoLogDbo> refreshTableDef(ConnectionDbo connectionToAnalyse, String table, ArrayList columns) throws SQLException {
		log.debug("Generate BusinessObject for table: " + table);
		ArrayList<RefreshInfoLogDbo> result = new ArrayList<RefreshInfoLogDbo>();
		
		TableDefDbo tableDbo = new TableDefDbo();
		tableDbo.setConId(connectionToAnalyse.getId());
		tableDbo.setName(table);
		
		boolean exist =  
			analyseDbDao.findBySqlReturnObject(
				TableDefDbo.class, 
				"SELECT * FROM " + TableDefDbo.TABLE + " WHERE " + TableDefDbo.CON_ID + " = ? AND " + TableDefDbo.NAME + " = ? " , 
				new Object[]{connectionToAnalyse.getId(), table}) != null;
		
		if (!exist) {
			analyseDbDao.save(tableDbo);
			RefreshInfoLogDbo infoLog = new RefreshInfoLogDbo();
			infoLog.setMessage("Add Table " + tableDbo.getName());
			result.add(infoLog);
		} else {
			log.debug("table " + tableDbo.getName() + " for connection " + connectionToAnalyse.getId() + " already exists");
		}
		return result;
	}
	
	
	private ArrayList getAllColumns(String schema, String table) {
		ArrayList result = new ArrayList();

		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {
			log.debug("execute select column query");
			stmt = connection.createStatement();
			sql= dbStatements.getAllColumnsSql(schema, table);
			
			
			log.debug("read all tables sql: " + sql);
			rset = stmt
					.executeQuery(sql);

			while (rset.next()) {
				Column col = new Column();
				col.setName(rset.getString("column_name"));
				col.setType(rset.getString("data_type"));
				result.add(col);
			}

		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;		
	}
}
